﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ERPNextHelper
{
    internal class Account_Excel
    {

        public object[] field_values = new object[11];
        //titles[0] = "account_name";
        //titles[1] = "disabled";
        //titles[2] = "account_number";
        //titles[3] = "root_type";
        //titles[4] = "report_type";
        //titles[5] = "account_currency";
        //titles[6] = "account_type";
        //titles[7] = "tax_rate";
        //titles[8] = "freeze_account";
        //titles[9] = "balance_must_be";
        //titles[10] = "include_in_gross";
       

        /// <summary>
        /// ///////////////////////////////////////////////////////////////////////////////////////
        /// </summary>
        public Account_Excel parent_account { get; set; }

        public List<Account_Excel> children { get; set; }=new List<Account_Excel>(){ };

        public int? lft { get; set; } = null;

        public int? rgt { get; set; } = null;

        //作为父科目的数量
        public int idx { get; set; } = 0;

        /// <summary>
        /// ////////////////////////////////////////////////////////////////////////////////////////
        /// </summary>
        public int[] account_number_split { get; set; }


        /// <summary>
        /// ////////////////////////////////////////////////////////////////////////////////////////
        /// </summary>

        void Make_Account_Splited()
        {
            account_number_split = new int[7] { 0, 0, 0, 0, 0, 0, 0 };
            string str = field_values[2].ToString();
            string left = "";
            for (int i = 0; i < 7; i++)
            {
                if (i < 2)
                {
                    if (str.Length >= 1)
                    {
                        left = str.Substring(0, 1);
                        str = str.Substring(1);
                        account_number_split[i] = Convert.ToInt32(left);
                    }
                    else
                    {
                        break;
                    }
                }

                else
                {
                    if (str.Length >= 2)
                    {
                        left = str.Substring(0, 2);
                        str = str.Substring(2);
                        account_number_split[i] = Convert.ToInt32(left);
                    }
                    else
                    {
                        break;
                    }
                }

                
            }
        }

        public static List<Account_Excel> MakeOrderedList(List<Account_Excel> origin)
        {
            foreach (Account_Excel item in origin)
            {
                item.Make_Account_Splited();
            }

            return origin
                .OrderBy((item) => item.account_number_split[0])
                .ThenBy(item => item.account_number_split[1])
                .ThenBy(item => item.account_number_split[2])
                .ThenBy(item => item.account_number_split[3])
                .ThenBy(item => item.account_number_split[4])
                .ThenBy(item => item.account_number_split[5])
                .ThenBy(item => item.account_number_split[6])
                .ToList();
        }

        public bool IsParent(Account_Excel account)
        {
            bool is_parent = true;

            string account_number1 = account.field_values[2].ToString();
            string account_number2 = this.field_values[2].ToString();
            if (account_number1.Length== account_number2.Length)
            {
                if(account_number1.Substring(account_number1.Length - 1) != "0")
                {
                    return false;
                }
            }


            for (int i = 6; i >= 0; i--)
            {
                if (account.account_number_split[i] != 0 && account.account_number_split[i] != this.account_number_split[i])
                {
                    is_parent = false;
                    break;
                }
            }
            return is_parent;
        }

        public static Account_Excel SetAllPrents(List<Account_Excel> accountsOrdered)
        {
            //
            Account_Excel root=new Account_Excel();
            

            //
            int len = accountsOrdered.Count;
            for (int i = 0; i < len; i++)
            {
                Account_Excel account = accountsOrdered[i];
                int j = i - 1;
                while (j >= 0)
                {
                    Account_Excel account2 = accountsOrdered[j];
                    if (account.IsParent(account2))
                    {
                        account.parent_account = account2;
                        account2.children.Add(account);
                        account2.idx++;
                        break;
                    }
                    j--;
                }
                if (account.parent_account == null)
                {
                    account.parent_account = root;
                    root.children.Add(account);
                }
            }

            return root;
        }

        public static List<Account_Excel> ReadFromExcelFile(string file)
        {
            List<Account_Excel> accounts = new List<Account_Excel>();

            using (var package = new ExcelPackage(new FileInfo(file)))
            {
                var sheet = package.Workbook.Worksheets[0];

                //获得列号
                int[] cols = new int[11];
                string[] titles = new string[11];
                titles[0] = "account_name";
                titles[1] = "disabled";
                titles[2] = "account_number";
                titles[3] = "root_type";
                titles[4] = "report_type";
                titles[5] = "account_currency";
                titles[6] = "account_type";
                titles[7] = "tax_rate";
                titles[8] = "freeze_account";
                titles[9] = "balance_must_be";
                titles[10] = "include_in_gross";
                for (int i = 0; i < 11; i++)
                {
                    int col = 1;
                    string i_title = titles[i];

                    while (true)
                    {
                        try
                        {
                            string title = sheet.Cells[1, col].Value.ToString();
                            if (title == i_title)
                            {
                                cols[i] = col;
                                break;
                            }
                            col++;
                        }
                        catch (Exception ex)
                        {
                            break;
                        }

                    }

                }

                //读入
                int row = 2;
                while (true)
                {
                    try
                    {
                        //account_name
                        string title = sheet.Cells[row, cols[0]].Value.ToString();
                        if (string.IsNullOrWhiteSpace(title))
                        {
                            break;
                        }

                        Account_Excel account = new Account_Excel();
                        account.field_values[0] = title;

                        //
                        for(int i = 1; i < 11; i++)
                        {
                            try
                            {
                                var obj = sheet.Cells[row, cols[i]].Value;                                
                                account.field_values[i] = obj;
                            }
                            catch(Exception ex)
                            {
                                continue;
                            }
                            
                        }




                        ///////////////////////////////////////
                        accounts.Add(account);
                        row++;
                    }
                    catch (Exception ex)
                    {
                        break;
                    }
                }











            }

            return accounts;
        }

        public static void WriteToExcelFile(string file, List<Account_Excel> accounts)
        {
            //公司名等共有字段
            string company = "上海研氢能源科技有限公司";
            string company_abbr = "研氢";
            string owner = "Administrator";
            string modified_by = "Administrator";

            //
            if (File.Exists(file))
            {
                File.Delete(file);
            }

            using (var package = new ExcelPackage(new FileInfo(file)))
            {


                var sheet=package.Workbook.Worksheets.Add("tabAccount");
                //title
                string[] titles = new string[23];
                titles[0] = "account_name";
                titles[1] = "disabled";
                titles[2] = "account_number";
                titles[3] = "root_type";
                titles[4] = "report_type";
                titles[5] = "account_currency";
                titles[6] = "account_type";
                titles[7] = "tax_rate";
                titles[8] = "freeze_account";
                titles[9] = "balance_must_be";
                titles[10] = "include_in_gross";

                titles[11] = "name";
                titles[12] = "creation";
                titles[13] = "modified";
                titles[14] = "modified_by";
                titles[15] = "owner";
                titles[16] = "docstatus";                
                titles[17] = "idx";
                titles[18] = "is_group";
                titles[19] = "company";
                titles[20] = "parent_account";
                titles[21] = "lft";
                titles[22] = "rgt";

               
                for (int i = 0; i < titles.Length; i++)
                {
                    sheet.Cells[1,i+1].Value= titles[i];
                }

                //数据
                int count=accounts.Count;
                for(int i=0;i<count; i++)
                {
                    //0-10
                    for(int j=0;j<11; j++)
                    {
                        sheet.Cells[i+2,j+1].Value= accounts[i].field_values[j];
                    }

                    //name
                    sheet.Cells[i + 2, 12].Value = $"{accounts[i].field_values[2]} - {accounts[i].field_values[0]} - {company_abbr}";

                    //creation
                    sheet.Cells[i + 2, 13].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffffff");

                    //modified
                    sheet.Cells[i + 2, 14].Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.ffffff");

                    //modified_by
                    sheet.Cells[i + 2, 15].Value = modified_by;

                    //owner
                    sheet.Cells[i + 2, 16].Value = owner;

                    //docstatus
                    sheet.Cells[i + 2, 17].Value = 0;

                    //idx
                    sheet.Cells[i + 2, 18].Value = accounts[i].idx;

                    //is_group
                    sheet.Cells[i + 2, 19].Value = accounts[i].idx>0?1:0;

                    //company
                    sheet.Cells[i + 2, 20].Value = company;

                    //parent_account
                    var parent_account=accounts[i].parent_account;
                    if (parent_account != null)
                    {
                        sheet.Cells[i + 2, 21].Value = $"{accounts[i].parent_account.field_values[2]} - {accounts[i].parent_account.field_values[0]} - {company_abbr}";
                    }

                    //lft
                    sheet.Cells[i + 2, 22].Value = accounts[i].lft;

                    //rgt
                    sheet.Cells[i + 2, 23].Value = accounts[i].rgt;


                }



                //保存
                package.Save();
            }
        }

        static int lft_rgt_no { get; set; }

        static void MakeLftRgt_recursion(Account_Excel root)
        {
            Account_Excel pt = root;            
            if (pt.children.Count == 0)//为叶子节点
            {
                lft_rgt_no++;
                pt.lft = lft_rgt_no;
                lft_rgt_no++;
                pt.rgt = lft_rgt_no;
                pt = pt.parent_account;
            }
            else
            {
                lft_rgt_no++;
                pt.lft= lft_rgt_no;

                foreach (var item in pt.children)
                {
                    MakeLftRgt_recursion(item);
                }

                lft_rgt_no++;
                pt.rgt= lft_rgt_no;
            }
        }

        public static void MakeLftRgt(Account_Excel root)
        {
            lft_rgt_no = -1;
            MakeLftRgt_recursion(root);
        }
    }
}
